﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GAMS.MODEL;

namespace KPIS.GERP.GAMS.DAL
{
    public class D_ACM_MAS_Account // : IDAL_ACM_MAS_Account
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_ACM_MAS_Account> GetList(Nullable<int> version_seq)
        {
            DataRowCollection drc = null;
            IList<M_ACM_MAS_Account> infoList = new List<M_ACM_MAS_Account>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@version_seq", version_seq);
                string strSQL = "SELECT account_seq,account_code,upper_account_seq,account_name,account_descr"
                               + ", version_seq ,concat(coalesce(account_code, ''), ' : ', coalesce(account_name, '')) as acc_name"
                               + ", gfmis_code, old_account_code, record_status, full_path, grouping_name, grouping_no"
                               + " FROM acm_mas_account "
                               + " WHERE record_status not like 'D'"
                               + (version_seq != null ? " and version_seq = @version_seq " : "")
                               + " ORDER BY account_code";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_MAS_Account info = new M_ACM_MAS_Account();
                        info.account_seq = Convert.ToInt32(drc[i]["account_seq"]);
                        info.account_code = drc[i]["account_code"].ToString();
                        info.upper_account_seq = cn.ConvertNullInt(drc[i]["upper_account_seq"]);
                        info.account_name = drc[i]["account_name"].ToString();
                        info.account_desc = drc[i]["account_descr"].ToString();
                        info.version_seq = drc[i]["version_seq"].ToString();
                        info.upper_account_name = drc[i]["acc_name"].ToString();
                        info.gfmis_code = drc[i]["gfmis_code"].ToString();
                        info.old_account_code = drc[i]["old_account_code"].ToString();
                        info.record_status = drc[i]["record_status"].ToString();
                        info.full_path = drc[i]["full_path"].ToString();
                        info.grouping_name = drc[i]["grouping_name"].ToString();
                        info.grouping_no = drc[i]["grouping_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;

        }

        public IList<M_ACM_MAS_Account> GetListForLookUp()
        {
            DataRowCollection drc = null;
            IList<M_ACM_MAS_Account> infoList = new List<M_ACM_MAS_Account>();
            try
            {
                dbManager.Open();

                string strSQL = "  SELECT account_seq,account_code,upper_account_seq,account_name,account_descr, acc.version_seq"
                    + ", concat(coalesce(account_code, ''), ' : ', coalesce(account_name, '')) as acc_name, grouping_name, grouping_no"
                    + "  FROM acm_mas_account acc  left  join acm_mas_account_version acc_ver on acc.version_seq = acc_ver.version_seq"
                    + "  WHERE (acc.record_status = 'U' ) and (acc_ver.use_flag = 1) and(acc_ver.record_status= 'U')"
                    + "  ORDER BY account_code";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_MAS_Account info = new M_ACM_MAS_Account();
                        info.account_seq = Convert.ToInt32(drc[i]["account_seq"]);
                        info.account_code = drc[i]["account_code"].ToString();
                        info.upper_account_seq = cn.ConvertNullInt(drc[i]["upper_account_seq"]);
                        info.account_name = drc[i]["account_name"].ToString();
                        info.account_desc = drc[i]["account_descr"].ToString();
                        info.version_seq = drc[i]["version_seq"].ToString();
                        info.upper_account_name = drc[i]["acc_name"].ToString();
                        info.grouping_name = drc[i]["grouping_name"].ToString();
                        info.grouping_no = drc[i]["grouping_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;

        }

        public IList<M_ACM_MAS_Account> GetListAccount()
        {
            return GetListAccount(null);
        }

        public IList<M_ACM_MAS_Account> GetListAccount(int? TranSeq)
        {
            DataRowCollection drc = null;
            IList<M_ACM_MAS_Account> infoList = new List<M_ACM_MAS_Account>();
            try
            {
                dbManager.Open();
                string strSQL = "SELECT acm1.account_seq, acm1.account_code, acm1.account_name"
                    + ", concat(coalesce(acm1.account_code, ''), ' : ', coalesce(acm1.account_name, '')) as acc_name "
                    + ", acm1.upper_account_seq,acmv.version_seq"
                    + " FROM acm_mas_account acm1"
                    + " LEFT JOIN acm_mas_account acm2 ON acm1.account_seq = acm2.upper_account_seq "
                        + " and acm2.record_status = 'U' "
                    + " Left Join acm_mas_account_version acmv ON acm1.version_seq = acmv.version_seq AND acmv.record_status = 'U' "
                        + " AND acmv.use_flag = 1 "
                    + (TranSeq != null ? " left join (select distinct account_seq"
                        + " from acm_transection_dtls"
                        + " where tran_seq = " + TranSeq.ToString()
                        + ") td on acm1.account_seq = td.account_seq" : "")
                    + " WHERE acm1.record_status = 'U' "
                    + " and acm2.account_seq is null "
                    + " and acmv.version_seq is not null "
                    + (TranSeq != null ? " and td.account_seq is null" : "")
                    + " ORDER BY acm1.account_code";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_MAS_Account info = new M_ACM_MAS_Account();
                        info.account_seq = Convert.ToInt32(drc[i]["account_seq"]);
                        info.account_code = drc[i]["account_code"].ToString();
                        info.account_code_name = drc[i]["acc_name"].ToString();
                        info.account_name = drc[i]["account_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }


        public IList<M_ACM_MAS_Account> GetListDataAccountFilter(string pFilter)
        {
            DataRowCollection drc = null;
            IList<M_ACM_MAS_Account> infoList = new List<M_ACM_MAS_Account>();
            try
            {
                dbManager.Open();
                string strSQL = " SELECT acm1.account_seq, acm1.account_code, acm1.account_name "
                            + " , concat(coalesce(acm1.account_code, ''), ' : ', coalesce(acm1.account_name, '')) as acc_name "
                            + " , acm1.upper_account_seq,acmv.version_seq "
                            + " FROM acm_mas_account  acm1 "
                            + " LEFT JOIN acm_mas_account  acm2 "
                            + " ON acm1.account_seq = acm2.upper_account_seq "
                            + " and acm2.record_status = 'U' "
                            + " Left Join acm_mas_account_version  acmv ON acm1.version_seq = acmv.version_seq AND acmv.record_status = 'U' "
                            + " AND acmv.use_flag = 1 "
                            + " WHERE acm1.record_status = 'U' "
                            + " and acm2.account_seq is null "
                            + " and acmv.version_seq is not null "
                            + " and concat(coalesce(acm1.account_code, ''), ' : ', coalesce(acm1.account_name, ''))   LIKE '%"+pFilter+"%'"
                            + " ORDER BY acm1.account_code ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_MAS_Account info = new M_ACM_MAS_Account();
                        info.account_seq = Convert.ToInt32(drc[i]["account_seq"]);
                        info.account_code = drc[i]["account_code"].ToString();
                        info.account_code_name = drc[i]["acc_name"].ToString();
                        info.account_name = drc[i]["account_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public M_ACM_MAS_Account GetInfo_ByAccountSeq_Version(Nullable<int> account_seq, Nullable<int> version_seq)
        {
            DataRowCollection drc = null;
            M_ACM_MAS_Account info = new M_ACM_MAS_Account();
            string strSQL = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@account_seq", account_seq);
                dbManager.AddParameters(1, "@version_seq", version_seq);
                strSQL = "SELECT account_seq, account_code, upper_account_seq, account_name"
                    + ", account_descr,record_status, version_seq, concat(coalesce(account_code, ''), ' : ', coalesce(account_name, '')) as acc_name"
                    + ", gfmis_code, old_account_code, grouping_name, grouping_no"
                    + " FROM acm_mas_account "
                    + " WHERE record_status = 'U'"
                    + (version_seq != null ? " and version_seq = @version_seq" : "")
                    + (account_seq != null ? " and account_seq = @account_seq " : "");
                
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.account_seq = Convert.ToInt32(drc[0]["account_seq"]);
                    info.account_code = drc[0]["account_code"].ToString();
                    info.upper_account_seq = cn.ConvertNullInt(drc[0]["upper_account_seq"]);
                    info.account_name = drc[0]["account_name"].ToString();
                    info.account_desc = drc[0]["account_descr"].ToString();
                    info.record_status = drc[0]["record_status"].ToString();
                    info.version_seq = drc[0]["version_seq"].ToString();
                    info.gfmis_code = drc[0]["gfmis_code"].ToString();
                    info.old_account_code = drc[0]["old_account_code"].ToString();
                    info.upper_account_name = drc[0]["acc_name"].ToString();
                    info.grouping_name = drc[0]["grouping_name"].ToString();
                    info.grouping_no = drc[0]["grouping_no"].ToString();
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_ACM_MAS_Account GetInfo_ByAccountSeqSearchName(Nullable<int> account_seq)
        {
            DataRowCollection drc = null;
            M_ACM_MAS_Account info = new M_ACM_MAS_Account();
            string strSQL = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@account_seq", account_seq);

                strSQL = "SELECT account_seq,account_name "
                        + "FROM acm_mas_account "
                        + "WHERE record_status = 'U' and account_seq = @account_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.account_name = drc[0]["account_name"].ToString();
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_ACM_MAS_Account GetListFillByAccountSeq(Nullable<int> account_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_ACM_MAS_Account info = new M_ACM_MAS_Account();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@account_seq", account_seq);

                strSQL = " SELECT account_seq,account_code,account_name ,concat(coalesce(account_code, ''), ' : ', coalesce(account_name, '')) as acc_name"
                        + " FROM  acm_mas_account "
                        + " WHERE account_seq = @account_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.account_code = drc[0]["account_code"].ToString();
                    info.account_code_name = drc[0]["acc_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public string GetFullPath(string account_seq, string strFullPath)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@account_seq", account_seq);

                strSQL = "SELECT upper_account_seq"
                        + " FROM acm_mas_account "
                        + " WHERE account_seq = @account_seq ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0 && !string.IsNullOrEmpty(dtb.Rows[0]["upper_account_seq"].ToString()))
                {
                    strFullPath = "/" + dtb.Rows[0]["upper_account_seq"].ToString() + strFullPath;
                    return GetFullPath(dtb.Rows[0]["upper_account_seq"].ToString(), strFullPath);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Dispose();
                }
            }

            return strFullPath;
        }

        //public M_ACM_MAS_Account GetInfo_ByAccountSeq(string account_seq)
        //{
        //    DataRowCollection drc = null;
        //    M_ACM_MAS_Account info = new M_ACM_MAS_Account();
        //    string strSQL = "";

        //    try
        //    {

        //        dbManager.Open();
        //        dbManager.CreateParameters(1);
        //        dbManager.AddParameters(0, "@account_seq", account_seq);
        //        strSQL = "SELECT acc.account_seq, acc.account_code, acc.upper_account_seq, acc.account_name, acc.account_descr" +
        //                 ", acc.record_status, acc.version_seq, acc.created_by , acc.created_when, acc.updated_by, acc.updated_when " +
        //                 "FROM acm_mas_account acc " +
        //                 "LEFT JOIN acm_mas_account acc2 ON acc.upper_account_seq = acc2.account_seq " +
        //                 "WHERE (acc.account_seq=@account_seq) and " +
        //                 "      (acc.record_status = 0)";
                
                        

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            info.account_seq = Convert.ToInt32(drc[0]["account_seq"]);
        //            info.account_code = drc[0]["account_code"].ToString();
        //            info.upper_account_seq = cn.ConvertNullInt(drc[0]["upper_account_seq"]);
        //            info.account_name = drc[0]["account_name"].ToString();
        //            info.account_desc = drc[0]["account_descr"].ToString();
        //            info.record_status = Convert.ToBoolean(drc[0]["record_status"]);
        //            info.version_seq = drc[0]["version_seq"].ToString();
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return info;
        //}

        //public M_ACM_MAS_Account GetInfo_ByAccountCode(string account_code, string version_seq)
        //{
        //    DataRowCollection drc = null;
        //    M_ACM_MAS_Account info = new M_ACM_MAS_Account();
        //    string strSQL = "";

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(2);
        //        dbManager.AddParameters(0, "@account_code", account_code);
        //        dbManager.AddParameters(1, "@version_seq", version_seq);
        //        strSQL = "SELECT acc.account_seq, acc.account_code, acc.upper_account_seq, acc.account_name, acc.account_descr" +
        //                 ", acc.record_status, acc.version_seq, acc.created_by , acc.created_when, acc.updated_by, acc.updated_when " +
        //                 "FROM acm_mas_account acc " +
        //                 "LEFT JOIN acm_mas_account acc2 ON acc.upper_account_seq = acc2.account_seq " +
        //                 "WHERE (acc.account_code=@account_code) and " +
        //                 "      (acc.version_seq=@version_seq) and " +
        //                 "      (acc.record_status = 0)";
        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            info.account_seq = Convert.ToInt32(drc[0]["account_seq"]);
        //            info.account_code = drc[0]["account_code"].ToString();
        //            info.upper_account_seq = cn.ConvertNullInt(drc[0]["upper_account_seq"]);
        //            info.account_name = drc[0]["account_name"].ToString();
        //            info.account_desc = drc[0]["account_descr"].ToString();
        //            info.record_status = Convert.ToBoolean(drc[0]["record_status"]);
        //            info.version_seq = drc[0]["version_seq"].ToString();
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return info;
        //}

        //public void GetAccountSeq()
        //{
        //    DataRowCollection drc = null;
        //    M_ACM_MAS_Account info = new M_ACM_MAS_Account();
        //    string strSQL = "";

        //    try
        //    {

        //        dbManager.Open();
        //        dbManager.CreateParameters(1);
        //        strSQL = "SELECT COUNT(account_seq)+1 as acc_seq" +
        //                 "FROM acm_mas_account " +
        //                 "GROUP BY account_seq ";

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            info.account_seq = Convert.ToInt32(drc[0]["acc_seq"]);
        //        }
        //        else
        //        {
        //            info.account_seq = 1;
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    // return info;
        //}

        //public void GetMaxVersion()
        //{
        //    DataRowCollection drc = null;
        //    M_ACM_MAS_Account info = new M_ACM_MAS_Account();
        //    string strSQL = "";

        //    try
        //    {

        //        dbManager.Open();
        //        strSQL = "SELECT MAX(version_seq)+1 as Seq_No " +
        //                 "FROM acm_mas_account ";

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            info.version_seq = drc[0]["Seq_No"].ToString();
        //        }
        //        else
        //        {
        //            info.account_seq = 1;
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    // return info;
        //}

        //public IList<M_ACM_MAS_Account> VersionNo()
        //{
        //    string strSQL = "";
        //    DataRowCollection drc = null;
        //    IList<M_ACM_MAS_Account> infoList = new List<M_ACM_MAS_Account>();


        //    try
        //    {
        //        dbManager.Open();
        //        strSQL = "SELECT version_seq " +
        //                 "FROM acm_mas_account " +
        //                 "WHERE record_status = 0 " +
        //                 "GROUP BY version_seq " +
        //                 "ORDER BY version_seq Desc";
        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

        //        if (drc.Count > 0)
        //        {
        //            for (int i = 0; i < drc.Count; i++)
        //            {
        //                M_ACM_MAS_Account info = new M_ACM_MAS_Account();
        //                info.version_seq = drc[i]["version_seq"].ToString();
        //                infoList.Add(info);
        //            }
        //        }
        //        else
        //        {
        //            M_ACM_MAS_Account info2 = new M_ACM_MAS_Account();
        //            info2.version_seq = "1";
        //            infoList.Add(info2);
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //    return infoList;
        //}

        //public IList<M_ACM_MAS_Account> GetFullRoot(string ver_no)
        //{
        //    DataRowCollection drc = null;
        //    IList<M_ACM_MAS_Account> infoList = new List<M_ACM_MAS_Account>();
        //    string strSQL = "";

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(1);
        //        dbManager.AddParameters(0, "@ver_no", ver_no);

        //        strSQL = "SELECT account_seq,account_code,upper_account_seq,account_name,account_descr" +
        //                 ",record_status,version_seq, created_by ,created_when,updated_by,updated_when " +
        //                 ", concat(coalesce(account_code, ''), ' : ', coalesce(account_name, '')) as full_name " +
        //                 "FROM acm_mas_account " +
        //                 "WHERE (version_seq=@ver_no) and " +
        //                 "      ((upper_account_seq is null) or (upper_account_seq =0) Or (upper_account_seq=''))" +
        //                 "      and (record_status = 0)";
        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            for (int i = 0; i < drc.Count; i++)
        //            {
        //                M_ACM_MAS_Account info = new M_ACM_MAS_Account();
        //                info.account_seq = Convert.ToInt32(drc[i]["account_seq"]);
        //                info.account_code = drc[i]["account_code"].ToString();
        //                info.upper_account_seq = cn.ConvertNullInt(drc[i]["upper_account_seq"]);
        //                info.account_name = drc[i]["account_name"].ToString();
        //                info.account_desc = drc[i]["account_descr"].ToString();
        //                info.record_status = Convert.ToBoolean(drc[i]["record_status"]);
        //                info.version_seq = drc[i]["version_seq"].ToString();
        //                info.full_name = drc[i]["full_name"].ToString();

        //                infoList.Add(info);
        //            }
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return infoList;
        //}

        //public IList<M_ACM_MAS_Account> GetFullParent(string strAccountSeq)
        //{
        //    Nullable<int> account_seq = cn.ConvertNullInt(strAccountSeq);
        //    DataRowCollection drc = null;
        //    IList<M_ACM_MAS_Account> infoList = new List<M_ACM_MAS_Account>();
        //    string strSQL = "";

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(1);
        //        dbManager.AddParameters(0, "@account_seq", account_seq);

        //        strSQL = "SELECT acc.account_seq, acc.account_code, acc.upper_account_seq, acc.account_name, acc.account_descr" +
        //                 ", acc.record_status, acc.version_seq, acc.created_by , acc.created_when, acc.updated_by, acc.updated_when " +
        //                 ", concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, '')) as full_name " +
        //                 ", acc2.count as child_count " +
        //                 "FROM acm_mas_account acc " +
        //                 "LEFT JOIN (SELECT upper_account_seq, count(*) as count " +
        //                            "FROM acm_mas_account " +
        //                            "WHERE (record_status = 0) " +
        //                            "GROUP BY upper_account_seq " +
        //                            ") acc2 ON acc.account_seq = acc2.upper_account_seq " +
        //                 "WHERE (acc.upper_account_seq=@account_seq) and " +
        //                 "      (acc.record_status = 0)";
        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            for (int i = 0; i < drc.Count; i++)
        //            {
        //                M_ACM_MAS_Account info = new M_ACM_MAS_Account();
        //                info.account_seq = Convert.ToInt32(drc[i]["account_seq"]);
        //                info.account_code = drc[i]["account_code"].ToString();
        //                info.upper_account_seq = cn.ConvertNullInt(drc[i]["upper_account_seq"]);
        //                info.account_name = drc[i]["account_name"].ToString();
        //                info.account_desc = drc[i]["account_descr"].ToString();
        //                info.record_status = Convert.ToBoolean(drc[i]["record_status"]);
        //                info.version_seq = drc[i]["version_seq"].ToString();
        //                info.full_name = drc[i]["full_name"].ToString();
        //                info.child_count = cn.ConvertNullInt(drc[i]["child_count"]);

        //                infoList.Add(info);
        //            }
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return infoList;
        //}

        public int Insert(M_ACM_MAS_Account info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@account_code", info.account_code);
                dbManager.AddParameters(1, "@upper_account_seq", info.upper_account_seq);
                dbManager.AddParameters(2, "@account_name", info.account_name);
                dbManager.AddParameters(3, "@account_desc", info.account_desc);
                dbManager.AddParameters(4, "@version_seq", info.version_seq);
                dbManager.AddParameters(5, "@record_status", info.record_status);
                dbManager.AddParameters(6, "@created_by", info.created_by);
                dbManager.AddParameters(7, "@created_when", info.created_when);
                dbManager.AddParameters(8, "@gfmis_code", info.gfmis_code);
                dbManager.AddParameters(9, "@old_account_code", info.old_account_code);
                dbManager.AddParameters(10, "@full_path", info.full_path);
                dbManager.AddParameters(11, "@grouping_name", info.grouping_name);
                dbManager.AddParameters(12, "@grouping_no", info.grouping_no);

                strSQL = "INSERT INTO acm_mas_account"
                    + " (account_code,upper_account_seq"
                    + ", gfmis_code, old_account_code"
                    + ", account_name,account_descr ,version_seq"
                    + ", record_status, created_by, created_when, full_path, grouping_name, grouping_no)"
                    + "  VALUES("
                    + "  @account_code"
                    + ", @upper_account_seq"
                    + ", @gfmis_code"
                    + ", @old_account_code"
                    + ", @account_name"
                    + ", @account_desc"
                    + ", @version_seq"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + ", @full_path"
                    + ", @grouping_name"
                    + ", @grouping_no"
                    + " );"
                    + " select scope_identity();";
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                var Seq = Convert.ToInt32(dt.Rows[0][0]);

                return Seq;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_ACM_MAS_Account info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@account_seq", info.account_seq);
                dbManager.AddParameters(1, "@account_code", info.account_code);
                dbManager.AddParameters(2, "@upper_account_seq", info.upper_account_seq);
                dbManager.AddParameters(3, "@account_name", info.account_name);
                dbManager.AddParameters(4, "@account_descr", info.account_desc);
                dbManager.AddParameters(5, "@version_seq", info.version_seq);
                dbManager.AddParameters(6, "@record_status", info.record_status);
                dbManager.AddParameters(7, "@updated_by", info.updated_by);
                dbManager.AddParameters(8, "@updated_when", info.updated_when);
                dbManager.AddParameters(9, "@gfmis_code", info.gfmis_code);
                dbManager.AddParameters(10, "@old_account_code", info.old_account_code);
                dbManager.AddParameters(11, "@full_path", info.full_path);
                dbManager.AddParameters(12, "@grouping_name", info.grouping_name);
                dbManager.AddParameters(13, "@grouping_no", info.grouping_no);

                strSQL = "UPDATE acm_mas_account"
                    + "  SET account_code = @account_code"
                    + ", upper_account_seq = @upper_account_seq"
                    + ", account_name = @account_name"
                    + ", account_descr = @account_descr"
                    + ", gfmis_code = @gfmis_code"
                    + ", old_account_code = @old_account_code"
                    + ", version_seq = @version_seq"
                    + ", record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + ", full_path = @full_path"
                    + ", grouping_name = @grouping_name"
                    + ", grouping_no = @grouping_no"
                    + " WHERE account_seq = @account_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(string AccountSeq, string EmpSeq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@account_seq", AccountSeq);
                dbManager.AddParameters(1, "@updated_by", EmpSeq);
                dbManager.AddParameters(2, "@updated_when", DateTime.Now);
                //dbManager.AddParameters(1, "@account_code", info.account_code);
                //dbManager.AddParameters(2, "@upper_account_seq", info.upper_account_seq);
                //dbManager.AddParameters(3, "@account_name", info.account_name);
                //dbManager.AddParameters(4, "@account_descr", info.account_desc);
                //dbManager.AddParameters(5, "@version_seq", info.version_seq);
                //dbManager.AddParameters(6, "@record_status", info.record_status);
                //dbManager.AddParameters(7, "@updated_by", info.updated_by);
                //dbManager.AddParameters(8, "@updated_when", info.updated_when);
                //dbManager.AddParameters(9, "@gfmis_code", info.gfmis_code);
                //dbManager.AddParameters(10, "@old_account_code", info.old_account_code);

                strSQL = "UPDATE acm_mas_account"
                    + "  SET record_status = 'D'"
                    //+ ", upper_account_seq = @upper_account_seq"
                    //+ ", account_name = @account_name"
                    //+ ", account_descr = @account_descr"
                    //+ ", gfmis_code = @gfmis_code"
                    //+ ", old_account_code = @old_account_code"
                    //+ ", version_seq = @version_seq"
                    //+ ", record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE account_seq = @account_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void SetNoneActiveVersion(M_ACM_MAS_Account info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@version_seq", info.version_seq);
                strSQL = " UPDATE acm_mas_account"
                      + " SET record_status = 'U' "
                      + " WHERE version_seq = @version_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
            }
        }
        public DataTable Report_ACCOUNT_Code()
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();

                //strSQL =  " SELECT accv.version_no,accv.version_descr,accv.record_status "
                //         +" ,accv.use_flag,acc.account_code,acc.account_name,acc.account_descr "
                //         +" ,acc.record_status,left(acc.account_code,1) as acc_type ,case left(acc.account_code, '1' )  when '1' then 'ทรัพย์สิน' "
                //         +" when  '2' then 'หนี้สิน'  when  '3' then 'ทุน'  when '4' then 'รายได้' "
                //         + " when '5' then 'ค่าใช้จ่าย'  end as group_account ,,acc.upper_account_seq"
                //         +" FROM acm_mas_account AS acc Inner Join acm_mas_account_version AS accv ON accv.version_seq = acc.version_seq "
                //         +" WHERE accv.record_status =  '1' AND accv.use_flag =  '1' AND "
                //         +" acc.record_status =  '1' "
                //         + " ORDER BY left(acc.account_code,1),acc.account_code ";
                strSQL = "SELECT accv.version_no,accv.version_descr,accv.record_status"
                    +", accv.use_flag,acc.account_code,acc.account_name,acc.account_descr ,acc.upper_account_seq"
                    +", acc.record_status,left(acc.account_code,1) as acc_type ,case left(acc.account_code, '1')  when '1' then 'ทรัพย์สิน'"
                    +" when  '2' then 'หนี้สิน'  when  '3' then 'ทุน'  when '4' then 'รายได้' "
                    +" when '5' then 'ค่าใช้จ่าย'  end as group_account"
                    + ", grouping_name"
                    +" FROM acm_mas_account AS acc"
                    + " Inner Join acm_mas_account_version AS accv ON accv.version_seq = acc.version_seq"
                    //+ " LEFT JOIN acm_mas_account as accup ON acc.upper_account_seq = accup.account_seq "
                    + " WHERE accv.record_status = 'U'"
                    + " AND accv.use_flag =  '1'"
                    + " AND acc.record_status =  'U' "
                    +" ORDER BY acc.account_code ";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        //public void ClearField(M_ACM_MAS_Account info)
        //{
        //    try
        //    {
        //        string strSQL = "SELECT account_seq,account_code,upper_account_seq,account_name " +
        //                 ", account_descr,record_status,version_seq,created_by " +
        //                 ", created_when,updated_by,updated_when " +
        //                 "FROM acm_mas_account " +
        //                 "WHERE 1=0";
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}


    }
}
